DROP VIEW dbo.view_fathers
GO

CREATE VIEW [dbo].[view_students_parents]
AS
	SELECT 	b.relationship, b.first_name + ' ' + b.last_name father_name, 
			c.first_name + ' ' + c.last_name mother_name, a.*
	FROM 	students a 
				LEFT OUTER JOIN parents b
					ON a.id = b.student_id  AND b.relationship = 1
				LEFT OUTER JOIN parents c ON a.id = c.student_id  AND c.relationship = 2
GO



            
     /*( select b.student_id, c.* 
                                          from students_2_parents b join parents c on b.parent_id  = c.id
                                             where relationship = 1 ) father on a.id = father.student_id
                                             */
     
     
/*
	select * from parents
	
     select * from view_fathers 
     
     select relationship, first_name, id from parents
     
     update parents
        set relationship = 2
        where id = 2
        
*/